import datetime
import json
from io import BytesIO

from flask import request, jsonify, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from sqlalchemy import func, and_, union, desc, text
from sqlalchemy.exc import IntegrityError

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.log_model import Log, Version
from tzq.document_management_system.model.permission_model import Permission
from tzq.document_management_system.model.property_model import Property
from tzq.extensions import db


# layui多条件分页查询接口
@dms_bp.route('/api/property', methods=['GET'])
def get_property():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")
    # 获取搜索参数 - 业务字段
    app_name = request.values.get("app_name")
    name = request.values.get("name")
    value = request.values.get("value")
    display_name = request.values.get("display_name")
    parent_path = request.values.get("parent_path")
    description = request.values.get("description")

    query = Property.query
    query = query.filter(Property.delete_flag == 'N')
    if principal: query = query.filter(Property.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Property.online_date == f'{online_date}')
    if created_by: query = query.filter(Property.created_by.ilike(f'%{created_by}%'))
    # 应用搜索条件 - 业务字段
    if app_name: query = query.filter(Property.app_name == app_name)
    if name: query = query.filter(Property.name.ilike(f'%{name}%'))
    if value: query = query.filter(Property.value.ilike(f'%{value}%'))
    if display_name: query = query.filter(Property.display_name.ilike(f'%{display_name}%'))
    if parent_path: query = query.filter(Property.parent_path.ilike(f'%{parent_path}%'))
    if description: query = query.filter(Property.description.ilike(f'%{description}%'))

    sort_field = request.args.get('field', 'property_id')
    sort_order = request.args.get('order', 'desc')
    if hasattr(Property, sort_field):
        if sort_order == 'asc':
            order_by = getattr(Property, sort_field).asc()
        else:
            order_by = getattr(Property, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(Property.online_date.desc(), Property.property_id.desc())
    pagination = query.paginate(page=page, per_page=limit, error_out=False)
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [dataInfo.to_dict() for dataInfo in pagination.items]
    })


def check_ip_modify_permission(client_ip_i: str):
    # 检查客户端用户，是否有新增、修改、删除权限
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员', '读写用户']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


def is_administrator(client_ip_i: str):
    # 检查用户IP是否为管理员
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


@dms_bp.route('/api/propertyUpdate', methods=['POST'])
def property_update_cell():
    # layui的table的单元格编辑，保存到后台
    try:
        # 判断是否有更新权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有更新权限，请联系管理员twx1014920！'}), 200
        data = request.json
        instance_temp = Property.query.get(data['id'])
        if not instance_temp:
            return jsonify({'status': 'error', 'message': 'Data Info not found'}), 404
        # 判断该条信息是否是创建人，同时用户不能为管理员。
        if instance_temp.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法修改！'}), 200

        v_old_value = ''
        v_new_value = data['value']

        if data['field'] == 'principal':
            v_old_value = instance_temp.principal
            instance_temp.principal = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'online_date':
            v_old_value = instance_temp.online_date.strftime('%Y-%m-%d')
            instance_temp.online_date = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'comment':
            v_old_value = instance_temp.comment
            instance_temp.comment = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        # 更新对应字段 - 业务字段
        if data['field'] == 'app_name':
            v_old_value = instance_temp.app_name
            instance_temp.app_name = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'name':
            v_old_value = instance_temp.name
            instance_temp.name = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'value':
            v_old_value = instance_temp.value
            instance_temp.value = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'display_name':
            v_old_value = instance_temp.display_name
            instance_temp.display_name = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'parent_path':
            v_old_value = instance_temp.parent_path
            instance_temp.parent_path = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'description':
            v_old_value = instance_temp.description
            instance_temp.description = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip

        app_name = ""
        if instance_temp.app_name == 'tmcCashplan':
            app_name = "计划"
        elif instance_temp.app_name == 'tmcInstruction':
            app_name = "指令"
        elif instance_temp.app_name == 'plancommon':
            app_name = "公共"
        elif instance_temp.app_name == 'monitorExport':
            app_name = "监控"
        elif instance_temp.app_name == 'TreasuryGuard':
            app_name = "eod"
        elif instance_temp.app_name == 'tmcDataCashplan':
            app_name = "接入层"
        elif instance_temp.app_name == 'monitorExportHis':
            app_name = "mon his"

        log = Log(
            business_type='字典-' + app_name,
            log_type='修改',
            ip=client_ip,
            field_name=data['field'],
            old_value=v_old_value,
            new_value=v_new_value,
        )
        db.session.add(log)
        db.session.commit()
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500


@dms_bp.route('/api/addProperty', methods=['POST'])
def add_property():
    # 添加数据接口
    data = request.get_json()
    client_ip = request.remote_addr
    # 判断是否有权限
    if not check_ip_modify_permission(client_ip):
        return jsonify({'status': 'error', 'message': f'你({client_ip})没有添加权限，请联系管理员twx1014920！'}), 200

    try:
        instance_temp = Property(
            # 业务字段
            app_name=data['app_name'],
            name=data['name'],
            value=data['value'],
            display_name=data['display_name'],
            parent_path=data['parent_path'],
            description=data['description'],

            principal=data['principal'],
            online_date=data['online_date_template'],
            comment=data['comment'],
            created_by=client_ip,
            last_updated_by=client_ip
        )
        db.session.add(instance_temp)
        db.session.commit()
        return jsonify({"code": 0, "msg": "添加成功"})
    except IntegrityError:
        return jsonify({"code": 1, "msg": "数据已存在"}), 400
    except Exception as e:
        return jsonify({"code": 2, "msg": str(e)}), 500


@dms_bp.route('/api/property/<int:id>', methods=['DELETE'])
def delete_property(id):
    # 软删除数据接口
    instance_temp = Property.query.get_or_404(id)
    try:
        # 判断是否有权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有删除权限，请联系管理员twx1014920！'}), 200
        # 判断该条信息是否是创建人；
        if instance_temp.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法删除！'}), 200
        v_old_value = json.dumps(instance_temp.to_dict())
        instance_temp.delete_flag = 'Y'
        instance_temp.last_update_date = func.now()
        instance_temp.last_updated_by = client_ip

        app_name = ""
        if instance_temp.app_name == 'tmcCashplan':
            app_name = "计划"
        elif instance_temp.app_name == 'tmcInstruction':
            app_name = "指令"
        elif instance_temp.app_name == 'plancommon':
            app_name = "公共"
        elif instance_temp.app_name == 'monitorExport':
            app_name = "监控"
        elif instance_temp.app_name == 'TreasuryGuard':
            app_name = "eod"
        elif instance_temp.app_name == 'tmcDataCashplan':
            app_name = "接入层"
        elif instance_temp.app_name == 'monitorExportHis':
            app_name = "mon his"

        log = Log(
            business_type='字典-' + app_name,
            log_type='删除',
            ip=client_ip,
            old_value=v_old_value,
        )
        db.session.add(log)
        db.session.commit()
        return jsonify({"code": 0, "msg": "删除成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@dms_bp.route('/api/exportProperty', methods=['GET', 'POST'])
def export_property():
    # 数据导出
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")
    # 获取搜索参数 - 业务字段
    app_name = request.values.get("app_name")
    name = request.values.get("name")
    value = request.values.get("value")
    display_name = request.values.get("display_name")
    parent_path = request.values.get("parent_path")
    description = request.values.get("description")

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    query = Property.query
    query = query.filter(Property.delete_flag == 'N')
    if principal: query = query.filter(Property.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Property.online_date == f'{online_date}')
    if created_by: query = query.filter(Property.created_by.ilike(f'%{created_by}%'))
    # 应用搜索条件 - 业务字段
    if app_name: query = query.filter(Property.app_name == app_name)
    if name: query = query.filter(Property.name.ilike(f'%{name}%'))
    if value: query = query.filter(Property.value.ilike(f'%{value}%'))
    if display_name: query = query.filter(Property.display_name.ilike(f'%{display_name}%'))
    if parent_path: query = query.filter(Property.parent_path.ilike(f'%{parent_path}%'))
    if description: query = query.filter(Property.description.ilike(f'%{description}%'))

    query = query.order_by(Property.online_date.desc(), Property.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)

    wb = Workbook()
    ws = wb.active
    ws.title = "Registry"

    # 写入表头
    headers = [
        'Name',
        'Value',
        'Description',
        'Index',
        'Display Name',
        'Status',
        'Parent Path',
        '应用名称', '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([
            item.name,
            item.value,
            item.description,
            '0',
            item.display_name,
            '1',
            item.parent_path,
            item.app_name,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 32  # Name
    ws.column_dimensions['B'].width = 32  # Value
    ws.column_dimensions['C'].width = 32  # Description
    ws.column_dimensions['D'].width = 12  # Index
    ws.column_dimensions['E'].width = 25  # Display Name
    ws.column_dimensions['F'].width = 12  # Status
    ws.column_dimensions['G'].width = 32  # Parent Path
    ws.column_dimensions['H'].width = 15  # 应用名称
    ws.column_dimensions['I'].width = 15  # 责任人
    ws.column_dimensions['J'].width = 15  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D', 'F', 'H', 'J']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    date_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    if online_date:
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            # 文件名不能为中文，不然报错了
            "Content-Disposition": "attachment; filename=jalor-Registry-" + app_name + "-"
                                   + version_string + date_string + ".xlsx"
        }
    )


def generate_excel_file(app_name_i: str, principal_i: str, online_date_i: str, created_by_i: str):
    app_name = app_name_i
    principal = principal_i
    online_date = online_date_i
    created_by = created_by_i
    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    query = Property.query
    query = query.filter(Property.delete_flag == 'N')
    if principal: query = query.filter(Property.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Property.online_date == f'{online_date}')
    if created_by: query = query.filter(Property.created_by.ilike(f'%{created_by}%'))
    # 应用搜索条件 - 业务字段
    if app_name: query = query.filter(Property.app_name == app_name)
    query = query.order_by(Property.online_date.desc(), Property.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    wb = Workbook()
    ws = wb.active
    ws.title = "Registry"
    # 写入表头
    headers = [
        'Name',
        'Value',
        'Description',
        'Index',
        'Display Name',
        'Status',
        'Parent Path',
        '应用名称', '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([
            item.name,
            item.value,
            item.description,
            '0',
            item.display_name,
            '1',
            item.parent_path,
            item.app_name,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 32  # Name
    ws.column_dimensions['B'].width = 32  # Value
    ws.column_dimensions['C'].width = 32  # Description
    ws.column_dimensions['D'].width = 12  # Index
    ws.column_dimensions['E'].width = 25  # Display Name
    ws.column_dimensions['F'].width = 12  # Status
    ws.column_dimensions['G'].width = 32  # Parent Path
    ws.column_dimensions['H'].width = 15  # 应用名称
    ws.column_dimensions['I'].width = 15  # 责任人
    ws.column_dimensions['J'].width = 15  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D', 'F', 'H', 'J']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return output


@dms_bp.route('/api/getPropertyOnlineDate', methods=['GET'])
def get_property_online_date():
    # 前端页面下拉框展示使用
    try:
        # 查询 delete_flag = 'N' 的 去重后 根据 online_date 倒叙排序的所有 online_date 的值
        data_list = db.session.query(Property.online_date.label('online_date')).filter(
            Property.delete_flag == 'N').distinct()
        subquery_b = db.session.query(Version.online_date.label('online_date')).distinct()
        union_query = union(data_list, subquery_b)
        result = db.session.execute(union_query.order_by(desc(text('online_date')))).fetchall()

        # 提取结果（因为返回的是元组列表）, 提取元组中的第一个元素
        online_date_data = [{"online_date": item[0].strftime('%Y-%m-%d')} for item in result]
        return jsonify({"code": 0, "msg": "", "count": len(online_date_data), "data": online_date_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
